﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffInfoEx_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffInfoEx_V2];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffInfoEx_V2]
    @xml xml
AS
BEGIN
SET NOCOUNT ON

/*
 * XML format:
 * <staffs>
 *  <s id="" />
 * </staffs>
 */

SELECT
    S.*,
    P.Position_ID,
    P.Position_name
FROM
    dbo.UDS_Staff S
    INNER JOIN dbo.UDS_Staff_In_Position SIP ON S.Staff_ID = SIP.Staff_ID
    INNER JOIN dbo.UDS_Position P ON SIP.Position_ID = P.Position_ID
    INNER JOIN @xml.nodes(N'/staffs/s') R(x) ON S.Staff_ID = R.x.value('@id', 'int')

END
GO